/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package controllers.sysAdmin;

import controllers.utils.DatabaseHjelper;
import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.inject.Named;
import javax.enterprise.context.SessionScoped;
import models.Department;

/**
 *
 * @author dybvik
 */
@SessionScoped
@Named(value="departmentList")
public class DepartmentList implements Serializable{
    private final List<Department> departments = new ArrayList<Department>();
    private final DatabaseHjelper dbh = DatabaseHjelper.hent();

    /** Creates a new instance of DepartmentList */
    public DepartmentList() {
    }

    public String navToDepartmentList(){
        updateDepartments();
        return "departmentList";
    }

    private void updateDepartments() {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet res = null;
        try {
            conn = dbh.finnForbindelse();
            stmt = conn.prepareStatement("select * from department");
            res = stmt.executeQuery();
            synchronized (departments) {
                departments.clear();
                while (res.next()){
                    Department dep = new Department();
                    dep.setDepartmentID(res.getInt("departmentID"));
                    dep.setName(res.getString("departmentName"));
                    dep.setAddress(res.getString("depAddress"));
                    dep.setPhoneNr(res.getString("depPhoneNr"));
                    dep.getInstitute().setInstituteID(res.getInt("instituteID"));
                    dep.setNote(res.getString("depNote"));
                    System.out.println(dep.toString());
                    departments.add(dep);
                }
                stmt = conn.prepareStatement("select * from institute where instituteID=?");
                for(Department dep: departments){
                    stmt.setInt(1, dep.getInstitute().getInstituteID());
                    res = stmt.executeQuery();
                    while(res.next()){
                        dep.getInstitute().setName(res.getString("instituteName"));
                    }
                }

                

            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        } finally {
            DatabaseHjelper.lukk(res);
            DatabaseHjelper.lukk(stmt);
            DatabaseHjelper.lukk(conn);
        }
    }

    public void executeChanges() {
        for (Department d : departments) {
            if (d.isEditable()) {
                saveChanges(d);
                d.setEditable(false);
            }
        }
    }

    private void saveChanges(final Department dep) {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = dbh.finnForbindelse();
            stmt = conn.prepareStatement("update department set departmentName=?, depAddress=?, depPhoneNr=?, instituteID=?, depNote=? WHERE departmentID=?");
            stmt.setString(1, dep.getName());
            stmt.setString(2, dep.getAddress());
            stmt.setString(3, dep.getPhoneNr());
            stmt.setInt(4, dep.getInstitute().getInstituteID());
            stmt.setString(5, dep.getNote());
            stmt.setInt(6, dep.getDepartmentID());
            stmt.executeUpdate();
        } catch (SQLException e) {
            System.out.println("Feil i lagring av Department: " + e);
        } finally {
            DatabaseHjelper.lukk(stmt);
            DatabaseHjelper.lukk(conn);
        }
    }

    public String setDeleteFlagAndNavigate(Department d) {
        d.setDeleteFlag(true);
        return "confirmDeleteDepartment";
    }

    public String executeDelete(){
        for (Department dep: departments) {
            if (dep.isDeleteFlag()) {
                deleteDepartment(dep);
                departments.remove(dep);
                break;
            }
        }
        return navToDepartmentList();
    }

    private void deleteDepartment(Department d){
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = dbh.finnForbindelse();
            stmt = conn.prepareStatement("DELETE FROM department WHERE departmentID=?");
            stmt.setInt(1, d.getDepartmentID());
            stmt.executeUpdate();
        } catch (SQLException e) {
            System.out.println("Feil i sletting av Department: " + e);
        } finally {
            DatabaseHjelper.lukk(stmt);
            DatabaseHjelper.lukk(conn);
        }
    }

    public String revertFlagsAndNavigate(){
        for(Department dep: departments){
            if(dep.isEditable()) dep.setEditable(false);
            if(dep.isDeleteFlag()) dep.setDeleteFlag(false);
        }
        return navToDepartmentList();
    }

    public List<Department> getDepartments() {
        return departments;
    }

}
